package goods_manage.service.impl;

import goods_manage.entity.GoodsDo;
import goods_manage.req.GoodsRequest;
import goods_manage.res.TableDTO;
import goods_manage.service.GoodsService;
import goods_manage.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

/**
 * StudentService的实现类
 */
public class GoodsServiceImpl implements GoodsService {
    @Override
    public TableDTO retrieveGoods(GoodsRequest request) {
        StringBuilder sql=new StringBuilder();
        sql.append("select * from goods ");
        if(request.getSearchKey()!=null&&!"".equals(request.getSearchKey().trim())){
            sql.append(" where type like '%"+request.getSearchKey().trim()+"%' ");
        }
        //分页查询
        sql.append("order by id asc limit ").append(request.getStart()).append(",").append(request.getPageSize());
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        TableDTO returnDTO=new TableDTO();
        try {
            conn=DBUtil.getConn();
            ps=conn.prepareStatement(sql.toString());
            rs= ps.executeQuery();
            //查询记录
            returnDTO.setData(fillData(rs));

            sql.setLength(0);
            sql.append("select count(*) from goods ");
            if(request.getSearchKey()!=null&&!"".equals(request.getSearchKey().trim())){
                sql.append(" where type like '%"+request.getSearchKey().trim()+"%' ");
            }
            ps=conn.prepareStatement(sql.toString());
            rs= ps.executeQuery();
            while(rs.next()){
                int count=rs.getInt(1);
                returnDTO.setTotalCount(count);
            }
            return returnDTO;

        }catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.closeRs(rs);
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
    }

    @Override
    public boolean add(GoodsDo goodsDo) {
        StringBuilder sql=new StringBuilder();
        sql.append(" insert into goods(name,price,purchase,sale,inventory,type) ");
        sql.append(" values(?,?,?,?,?,?) ");
        Connection conn=null;
        PreparedStatement ps=null;
        try {
            conn=DBUtil.getConn();
            ps=conn.prepareStatement(sql.toString());
            ps.setString(1, goodsDo.getName());
            ps.setString(2, goodsDo.getPrice());
            ps.setString(3, goodsDo.getPurchase());
            ps.setString(4, goodsDo.getSale());
            ps.setString(5, goodsDo.getInventory());
            ps.setString(6, goodsDo.getType());
            return ps.executeUpdate()==1;
        }catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
    }

    @Override
    public GoodsDo getById(int selectedStudentId) {
        StringBuilder sql=new StringBuilder("select * from goods where id=? ");
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        GoodsDo goodsDo =new GoodsDo();
        try {
            conn=DBUtil.getConn();
            ps=conn.prepareStatement(sql.toString());
            ps.setInt(1,selectedStudentId);
            rs=ps.executeQuery();

            while(rs.next()){
                //处理每一条查询
                int id= rs.getInt("id");
                String name= rs.getString("name");
                String price= rs.getString("price");
                String purchase= rs.getString("purchase");
                String sale= rs.getString("sale");
                String inventory= rs.getString("inventory");
                String type= rs.getString("type");
                goodsDo.setId(id);
                goodsDo.setName(name);
                goodsDo.setPrice(price);
                goodsDo.setPurchase(purchase);
                goodsDo.setSale(sale);
                goodsDo.setInventory(inventory);
                goodsDo.setType(type);
            }

            return goodsDo;
        }catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.closeRs(rs);
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
    }

    @Override
    public boolean update(GoodsDo goodsDo) {
        StringBuilder sql=new StringBuilder();
        sql.append(" update goods set name=?,price=?,purchase=?,sale=?,inventory=?,type=? ");
        sql.append(" where id =? ");
        Connection conn=null;
        PreparedStatement ps=null;
        try {
            conn=DBUtil.getConn();
            ps=conn.prepareStatement(sql.toString());
            ps.setString(1, goodsDo.getName());
            ps.setString(2, goodsDo.getPrice());
            ps.setString(3, goodsDo.getPurchase());
            ps.setString(4, goodsDo.getSale());
            ps.setString(5, goodsDo.getInventory());
            ps.setString(6, goodsDo.getType());
            ps.setInt(7, goodsDo.getId());
            return ps.executeUpdate()==1;
        }catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
    }

    @Override
    public boolean delete(int[] selectedGoodsIds) {
        StringBuilder sql=new StringBuilder();
        sql.append(" delete from goods where id in ( ");
        for (int i = 0; i < selectedGoodsIds.length; i++) {
            if(i==selectedGoodsIds.length-1){
                sql.append(" ? ");
            }else{
                sql.append(" ?, ");
            }
        }
        sql.append(" ) ");
        Connection conn=null;
        PreparedStatement ps=null;
        try {
            conn=DBUtil.getConn();
            ps=conn.prepareStatement(sql.toString());

            for (int i = 0; i < selectedGoodsIds.length; i++) {
                //设置参数,从1开始
                ps.setInt(i+1,selectedGoodsIds[i]);
            }
            return ps.executeUpdate()==selectedGoodsIds.length;
        }catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
    }

    private static Vector<Vector<Object>> fillData(ResultSet rs) throws SQLException {
        Vector<Vector<Object>>data =new Vector<>();
        while(rs.next()){
            //处理每一条查询
            Vector<Object>oneRecord=new Vector<>();
            int id= rs.getInt("id");
            String name= rs.getString("name");
            String price= rs.getString("price");
            String purchase= rs.getString("purchase");
            String sale= rs.getString("sale");
            String inventory= rs.getString("inventory");
            String type= rs.getString("type");
            oneRecord.addElement(id);
            oneRecord.addElement(name);
            oneRecord.addElement(price);
            oneRecord.addElement(purchase);
            oneRecord.addElement(sale);
            oneRecord.addElement(inventory);
            oneRecord.addElement(type);
            data.addElement(oneRecord);
        }
        return data;
    }

}
